/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO.MySQL;

import ClassesUnicas.Profissao;
import DAO.DAOProfissao;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.Types;
import java.util.ArrayList;

/**
 * DAOProfissaoMySQL
 *
 * Versao 1.0
 *
 * Data 24/03/2013
 *
 * @author Igor
 */
public class DAOProfissaoMySQL extends DAOConexaoMySQL implements DAOProfissao {

    @Override
    public String gravaProfissao(Profissao profissao) throws Exception {
        String vReturn = ""; //Representa que nao achou o profissional

        CallableStatement cs;
        cs = this.con.getCon().prepareCall("{ ? = call GRAVA_PROFISSAO(?,?)}");

        cs.registerOutParameter(1, Types.NUMERIC);
        cs.setLong(2, profissao.getIdProfissao());
        cs.setString(3, profissao.getDescricao());

        cs.execute();

        vReturn = cs.getString(1);
        this.con.getStm().executeBatch();

        return vReturn;
    }

    @Override
    public Profissao localizaProfissao(long idProfissao) throws Exception {
        ResultSet result = null;
        Profissao profissao = null;

        result = this.select.executeQuery("select\n"
                + "a.ID_PROFISSAO,\n"
                + "a.DESCRICAO\n"
                + "from profissao a\n"
                + "where a.ID_PROFISSAO = " + idProfissao);

        while (result.next()) {
            profissao = new Profissao();

            profissao.setIdProfissao(result.getLong("ID_PROFISSAO"));
            profissao.setDescricao(result.getString("DESCRICAO"));
        }

        return profissao;
    }

    @Override
    public Profissao localizaProfissao(String descricao) throws Exception {
        ResultSet result = null;
        Profissao profissao = null;

        result = this.select.executeQuery("select\n"
                + "a.ID_PROFISSAO,\n"
                + "a.DESCRICAO\n"
                + "from profissao a\n"
                + "where a.DESCRICAO = " + descricao);

        while (result.next()) {
            profissao = new Profissao();

            profissao.setIdProfissao(result.getLong("ID_PROFISSAO"));
            profissao.setDescricao(result.getString("DESCRICAO"));
        }

        return profissao;
    }

    @Override
    public ArrayList<Profissao> localizaProfissoes(String idProfissao, String descricao, String orderBy) throws Exception {
        ResultSet result = null;
        ArrayList<Profissao> profissoes = new ArrayList<Profissao>();
        String clausulaWhere = "";
        String clausulaOrderBy = "";

        //Cria clausula where
        if (idProfissao != null) {
            if (clausulaWhere.isEmpty()) {
                clausulaWhere = "where ";
            } else {
                clausulaWhere = clausulaWhere + " and ";
            }
            clausulaWhere = clausulaWhere + " a.ID_PROFISSAO = " + idProfissao;
        }

        if (descricao != null) {
            if (clausulaWhere.isEmpty()) {
                clausulaWhere = "where ";
            } else {
                clausulaWhere = clausulaWhere + " and ";
            }
            clausulaWhere = clausulaWhere + " a.DESCRICAO like '%" + descricao + "%'";
        }

        //OrderBy
        if (orderBy != null) {
            clausulaOrderBy = "order by " + orderBy;
        }

        result = this.select.executeQuery("select\n"
                + "a.ID_PROFISSAO,\n"
                + "a.DESCRICAO\n"
                + "from profissao a\n"
                + clausulaWhere
                + clausulaOrderBy);

        while (result.next()) {
            Profissao profissao = new Profissao();

            profissao.setIdProfissao(result.getLong("ID_PROFISSAO"));
            profissao.setDescricao(result.getString("DESCRICAO"));
            profissoes.add(profissao);

        }

        return profissoes;
    }

}
